import Database from 'better-sqlite3'
import { join } from 'path'
import { app } from 'electron'

/**
 * 数据库连接管理
 */
class DatabaseConnection {
  private db: Database.Database | null = null
  private readonly dbPath: string

  constructor() {
    // 获取用户数据目录
    const userDataPath = app.getPath('userData')
    this.dbPath = join(userDataPath, 'photo-plover-store.db')
  }

  /**
   * 获取数据库连接
   * @returns 数据库实例
   */
  public getConnection(): Database.Database {
    if (!this.db) {
      this.db = new Database(this.dbPath)
      this.db.pragma('journal_mode = WAL')
      this.initializeTables()
    }
    return this.db
  }

  /**
   * 关闭数据库连接
   */
  public closeConnection(): void {
    if (this.db) {
      this.db.close()
      this.db = null
    }
  }

  /**
   * 初始化数据库表
   */
  private initializeTables(): void {
    // 创建照片表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS photos (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        file_path TEXT NOT NULL UNIQUE,
        file_name TEXT NOT NULL,
        file_size INTEGER NOT NULL,
        file_hash TEXT NOT NULL,
        width INTEGER,
        height INTEGER,
        format TEXT,
        created_at DATETIME NOT NULL,
        modified_at DATETIME NOT NULL,
        captured_at DATETIME,
        camera_make TEXT,
        camera_model TEXT,
        latitude REAL,
        longitude REAL,
        altitude REAL,
        thumbnail_path TEXT,
        preview_path TEXT,
        rating INTEGER DEFAULT 0,
        description TEXT
      )
    `)

    // 创建相册表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS albums (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        description TEXT,
        cover_photo_id INTEGER,
        created_at DATETIME NOT NULL,
        updated_at DATETIME NOT NULL,
        FOREIGN KEY (cover_photo_id) REFERENCES photos(id)
      )
    `)

    // 创建照片相册关联表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS photo_albums (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        photo_id INTEGER NOT NULL,
        album_id INTEGER NOT NULL,
        added_at DATETIME NOT NULL,
        FOREIGN KEY (photo_id) REFERENCES photos(id),
        FOREIGN KEY (album_id) REFERENCES albums(id)
      )
    `)

    // 创建标签表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        color TEXT,
        created_at DATETIME NOT NULL
      )
    `)

    // 创建照片标签关联表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS photo_tags (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        photo_id INTEGER NOT NULL,
        tag_id INTEGER NOT NULL,
        FOREIGN KEY (photo_id) REFERENCES photos(id),
        FOREIGN KEY (tag_id) REFERENCES tags(id)
      )
    `)

    // 创建用户设置表
    this.db!.exec(`
      CREATE TABLE IF NOT EXISTS settings (
        key TEXT PRIMARY KEY,
        value TEXT NOT NULL,
        updated_at DATETIME NOT NULL
      )
    `)

    // 创建索引以提高查询性能
    this.db!.exec(`
      CREATE INDEX IF NOT EXISTS idx_photos_file_path ON photos(file_path);
      CREATE INDEX IF NOT EXISTS idx_photos_captured_at ON photos(captured_at);
      CREATE INDEX IF NOT EXISTS idx_photos_file_hash ON photos(file_hash);
      CREATE INDEX IF NOT EXISTS idx_albums_name ON albums(name);
      CREATE INDEX IF NOT EXISTS idx_tags_name ON tags(name);
      CREATE INDEX IF NOT EXISTS idx_photo_albums_photo_id ON photo_albums(photo_id);
      CREATE INDEX IF NOT EXISTS idx_photo_albums_album_id ON photo_albums(album_id);
      CREATE INDEX IF NOT EXISTS idx_photo_tags_photo_id ON photo_tags(photo_id);
      CREATE INDEX IF NOT EXISTS idx_photo_tags_tag_id ON photo_tags(tag_id);
    `)
  }
}

// 导出单例实例
export const dbConnection = new DatabaseConnection()

// 应用退出时关闭数据库连接
app.on('before-quit', () => {
  dbConnection.closeConnection()
})